from datetime import datetime

import mysql


def parse_date(date_str):
    if date_str is None:
        return None
    try:
        return datetime.strptime(date_str, "%Y-%m-%d").date()
    except ValueError as e:
        print(f"Error parsing date: {date_str}. Error: {e}")
        return None


def get_alert_threshold_days(connection):
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT config_value FROM alert_config WHERE config_key = 'ALERT_THRESHOLD_DAYS'")
        result = cursor.fetchone()
        if result:
            return int(result[0])  # Return the config_value as an integer
        else:
            return 10  # Default value if not found
    except mysql.connector.Error as e:
        print(f"Error reading alert threshold days: {e}")
        return 10  # Return a default value in case of error


def get_sync_interval_minutes(connection):
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT config_value FROM alert_config WHERE config_key = 'SYNC_INTERVAL_MINUTES'")
        result = cursor.fetchone()
        if result:
            return int(result[0])  # Return the config_value as an integer
        else:
            return 3  # Default value if not found
    except mysql.connector.Error as e:
        print(f"Error reading sync interval minutes: {e}")
        return 3  # Return a default value in case of error


def sync_task_alerts():
    from app import connection  # Move the import here to avoid circular import issues
    try:
        # Check if the connection is still alive
        if not connection.is_connected():
            connection.reconnect(attempts=3, delay=2)  # Reconnect with retries if the connection is lost

        print("Connected to MySQL database")

        alert_threshold_days = get_alert_threshold_days(connection)
        print(f"Alert threshold days: {alert_threshold_days}")

        cursor = connection.cursor()

        # Truncate the task_alert table to remove all existing data
        cursor.execute("DELETE FROM task_alert")

        print("Existing task alerts have been truncated.")

        cursor.execute(
            "SELECT id, customer_number, schedule_date, specification, quantity, drawing_number, planned_date, "
            "remarks, sale_man_id, is_urgent FROM production_task WHERE is_done = 0 ORDER BY planned_date ASC")
        tasks = cursor.fetchall()

        order_index = 1

        for task in tasks:
            task_id = task[0]
            customer_number = task[1]
            schedule_date = task[2]
            specification = task[3]
            quantity = task[4]
            drawing_number = task[5]
            planned_date = parse_date(task[6])
            remarks = task[7]
            sale_man_id = task[8]
            is_urgent = task[9]

            alert_level = 1  # Default to low

            if is_urgent or (
                    planned_date and (planned_date - datetime.today().date()).days <= alert_threshold_days):
                alert_level = 3  # Set to high alert

            task_alert = (
                task_id,
                customer_number,
                schedule_date,
                specification,
                quantity,
                drawing_number,
                planned_date,
                remarks,
                sale_man_id,
                is_urgent,
                order_index,
                alert_level  # Include alert_level in the tuple
            )

            insert_query = """INSERT INTO task_alert (id, customer_number, schedule_date, specification, quantity, 
            drawing_number, planned_date, remarks, sale_man_id, is_urgent, order_index, alert_level) VALUES (%s, %s, 
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """
            cursor.execute(insert_query, task_alert)

            order_index += 1

        connection.commit()
        print("Task alerts synchronized successfully with order indexes and alert levels.")

    except mysql.connector.Error as e:
        print(f"Error: {e}")
    finally:
        if connection.is_connected():
            cursor.close()  # It's better to close the cursor instead of the connection
            print("MySQL cursor is closed")


def clean_expired_tokens():
    """
    清理过期的 tokens 数据
    """
    from app import connection  # 避免循环导入

    try:
        # 检查数据库连接是否可用
        if not connection.is_connected():
            connection.reconnect(attempts=3, delay=2)  # 如果连接中断，重试连接

        print("Connected to MySQL database for cleaning expired tokens.")

        cursor = connection.cursor()

        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        # 删除过期的 token
        delete_query = "DELETE FROM user_tokens WHERE expiry_time < %s"
        cursor.execute(delete_query, (current_time,))

        # 提交事务
        connection.commit()

        print(f"{cursor.rowcount} expired tokens deleted successfully.")

    except mysql.connector.Error as e:
        print(f"Error cleaning expired tokens: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            print("MySQL cursor is closed after cleaning expired tokens.")
